/*  ------------------------------------------------------------------------  */
/*  Paper        : Does Stakeholder Outrage Determine Executive Pay?          */
/*  Program      : a01-09-laylineInsider.do                                   */
/*  Description  : Insider trading reports                                    */
/*  ------------------------------------------------------------------------  */

/*  ------------------------------------------------------------------------  */
//  Insider trading reports

#delimit ;
import delimited "$layline/20231117/lit_panel.csv",
	bindquote(strict) varnames(1) case(preserve)
	stringcols(_all) clear
; #delimit cr


//  Change dates from string to date format
foreach var of varlist filingDate periodOfReport {
gen long _`var' = date(`var', "YMD") 
order _`var', after(`var')
drop `var'
rename _`var' `var'
format `var' %tdCCYY-NN-DD
}

keep if inrange(year(periodOfReport),2017,2018)
keep if type=="3"
// duplicates list *

foreach var of varlist m_isDirector m_isOfficer m_isTenPercentOwner m_isOther {
destring `var', replace
}

summ m_isDirector m_isOfficer m_isTenPercentOwner m_isOther 
keep if m_isTenPercentOwner==1

keep URL acceptanceDatetime accessionNumber filingDate issuerCIK periodOfReport m_isDirector m_isOfficer m_isTenPercentOwner m_isOther
duplicates drop *, force

duplicates list accessionNumber

save "$temp112/form3.dta", replace

/*  ---------------------------  */

#delimit ;
import delimited "$layline/20231117/lit_reportingowner.csv",
	bindquote(strict) varnames(1) case(preserve)
	stringcols(_all) clear
; #delimit cr

keep accessionNumber rptOwnerCik rptOwnerName isDirector isOfficer isTenPercentOwner isOther officerTitle otherText
format rptOwnerName officerTitle otherText %25s 

/*  ---------------------------  */
//  Data cleaning step
//  Sometimes these variables are "0/1" or "false/true"

foreach isvar in isDirector isOfficer isTenPercentOwner isOther {
	di _newline "-- Original values for: `isvar'" _newline
	tab `isvar'
	replace `isvar' = "0" if lower(`isvar') == "false" | missing(`isvar')
	replace `isvar' = "1" if lower(`isvar') == "true"
	destring `isvar', replace
	di _newline "-- Updated values for: `isvar'" _newline
	tab `isvar'
}

	keep if isTenPercentOwner==1
	duplicates drop *, force
	save "$temp112/form3Owners_tmp.dta", replace

keep accessionNumber rptOwnerCik isDirector isOfficer isTenPercentOwner isOther
	duplicates drop *, force
	save "$temp112/form3Owners.dta", replace


/*  ------------------------------------------------------------------------  */
//  Institutional investors

#delimit ;
import delimited "$layline/20231117/lbh_institutional.csv",
	bindquote(strict) varnames(1) case(preserve)
	stringcols(_all) clear
; #delimit cr


//  Change dates from string to date format
foreach var of varlist period {
gen long _`var' = date(`var', "YMD") 
order _`var', after(`var')
drop `var'
rename _`var' `var'
format `var' %tdCCYY-NN-DD
}

keep if inrange(year(period),2017,2018)
keep cik
duplicates drop *, force

save "$temp112/instos.dta", replace

/*  ------------------------------------------------------------------------  */
//  Merge datasets

use "$temp112/form3.dta", clear


merge 1:m accessionNumber using "$temp112/form3Owners.dta"
	keep if _merge==3
	drop _merge
	drop acceptanceDatetime filingDate

gen cik = rptOwnerCik

merge m:1 cik using "$temp112/instos.dta"
	drop if _merge==2
	gen isInsto = _merge==3
	tab isInsto
	drop _merge

keep accessionNumber issuerCIK rptOwnerCik isDirector isOfficer isTenPercentOwner isOther isInsto
duplicates drop *, force

sort issuerCIK

by accessionNumber, sort: egen maxInsto = max(isInsto)

by issuerCIK, sort: egen f3_accession = nvals(accessionNumber)
by issuerCIK: egen f3_rptOwner = nvals(rptOwnerCik)
by issuerCIK: egen f3_nonInstoAcc = nvals(accessionNumber) if maxInsto ==0
by issuerCIK: egen f3_nonInsto = nvals(rptOwnerCik) if isInsto !=1
by issuerCIK: egen f3_YESInstoAcc = nvals(accessionNumber) if maxInsto ==1
by issuerCIK: egen f3_YESInsto = nvals(rptOwnerCik) if isInsto ==1

//  Carry over the IssuerCIK to manage missing values
by issuerCIK: egen f3_filings = max(f3_accession)
by issuerCIK: egen f3_owners = max(f3_rptOwner)
by issuerCIK: egen f3_NIfilings = max(f3_nonInstoAcc)
by issuerCIK: egen f3_NIowners = max(f3_nonInsto)
by issuerCIK: egen f3_Ifilings = max(f3_YESInstoAcc)
by issuerCIK: egen f3_Iowners = max(f3_YESInsto)

foreach var of varlist isDirector isOfficer isTenPercentOwner {
	by issuerCIK: egen c_`var'  = max(`var')
}

keep issuerCIK f3_filings f3_owners f3_NIfilings f3_NIowners f3_Ifilings f3_Iowners c_isDirector c_isOfficer c_isTenPercentOwner

foreach var of varlist f3_filings f3_owners f3_NIfilings f3_NIowners f3_Ifilings f3_Iowners c_isDirector c_isOfficer c_isTenPercentOwner {
	replace `var' = 0 if missing(`var')
}
duplicates drop *, force

save "$temp112/FinalForm3.dta", replace

/*  ------------------------------------------------------------------------  */
//  Activist investors

#delimit ;
import delimited "$layline/20231117/lbh_blockholders.csv",
	bindquote(strict) varnames(1) case(preserve)
	stringcols(_all) clear
; #delimit cr


keep if type=="SC 13D"

//  Change dates from string to date format
foreach var of varlist filingDate {
gen long _`var' = date(`var', "YMD") 
order _`var', after(`var')
drop `var'
rename _`var' `var'
format `var' %tdCCYY-NN-DD
}


keep if inrange(year(filingDate),2017,2018)
keep accessionNumber subjectCik ownerCik
duplicates drop *, force

by subjectCik, sort: egen actFilings = nvals(accessionNumber)
by subjectCik, sort: egen actOwners = nvals(ownerCik)

keep subjectCik actFilings actOwners
duplicates drop *, force

summ actFilings actOwners

save "$temp112/activists.dta", replace

/*  ------------------------------------------------------------------------  */
//  Non-management proxy filings

#delimit ;
import delimited "$layline/20231117/lbh_proxies.csv",
	bindquote(strict) varnames(1) case(preserve)
	stringcols(_all) clear
; #delimit cr


//  Change dates from string to date format
foreach var of varlist filingDate {
gen long _`var' = date(`var', "YMD") 
order _`var', after(`var')
drop `var'
rename _`var' `var'
format `var' %tdCCYY-NN-DD
}


keep if inrange(year(filingDate),2017,2018)
keep accessionNumber subjectCik filerCik
duplicates drop *, force

by subjectCik, sort: egen proxyFilings = nvals(accessionNumber)
by subjectCik, sort: egen proxyOwners = nvals(filerCik)

keep subjectCik proxyFilings proxyOwners
duplicates drop *, force

summ proxyFilings proxyOwners

save "$temp112/proxies.dta", replace

/*  ------------------------------------------------------------------------  */
//  Non-management proxy filings by instos

#delimit ;
import delimited "$layline/20231117/lbh_proxies.csv",
	bindquote(strict) varnames(1) case(preserve)
	stringcols(_all) clear
; #delimit cr


//  Change dates from string to date format
foreach var of varlist filingDate {
gen long _`var' = date(`var', "YMD") 
order _`var', after(`var')
drop `var'
rename _`var' `var'
format `var' %tdCCYY-NN-DD
}


keep if inrange(year(filingDate),2017,2018)
keep accessionNumber subjectCik filerCik
duplicates drop *, force

rename filerCik cik
merge m:1 cik using "$temp112/instos.dta"
	drop if _merge==2
	gen isInsto = _merge==3
	tab isInsto
	keep if _merge==3
	drop _merge
// 	tab type

by subjectCik, sort: egen proxyFilingsInsto = nvals(accessionNumber)

keep subjectCik proxyFilingsInsto
duplicates drop *, force

summ proxyFilingsInsto

save "$temp112/proxiesInsto.dta", replace

/*  ------------------------------------------------------------------------  */
//  Check datasets

use "$temp112/FinalForm3.dta", clear

use "$temp112/activists.dta", clear

use "$temp112/proxies.dta", clear


/*  -|----------------------------------------------------------------------  */
/*  ||  ---    Program end: a01-09-laylineInsider.do                          */
/*  -|  ---    Attila Balogh    --------------------------------------------  */